const oracledb = require('oracledb')

const xlsx = require('node-xlsx');
var fs = require('fs');

var moment = require('moment');

const DB_CONFIG = {
  user: 'cgxzcuser', 
  password: 'Tp3D1rBz#cx2024',
  //connectString: 'jdbc:oracle:thin:@172.50.100.181:1521:cgxzc' // 数据库地址：{IP:PORT/数据库名称}
  connectString: '172.50.100.181:1521/cgxzc' // 数据库地址：{IP:PORT/数据库名称}
}


console.log('运行模式:', oracledb.thin ? 'Thin' : 'Thick');

//oracledb.initOracleClient();
oracledb.initOracleClient({libDir: "C:\\Users\\15371\\scoop\\apps\\oracle-instant-client\\current"});



const 老产销 = `
select * from
    SLM_ORDER_HEAD@TGCXDB_105 t
        INNER JOIN SLM_ORDER_LINE@TGCXDB_105 T1 ON T.ORD_PK = T1.ORD_PK
        INNER JOIN SLM_ORDER_LINE_DELIVERY@TGCXDB_105 T2 ON T.ORD_PK = T2.ORD_PK
        AND T1.ORD_LN_PK = T2.ORD_LN_PK
where ROWNUM<10
`

const orderFields = [
    // 订单头字段
    "ORD_PK", "MANAG_NO", "ORDER_NO", "CUSTM_ORDER_NO", "REVW_RPT_NO", "ORDER_TYP", "ORDER_TYP_DESC", "PROTCL_NO", "CUSTOMER_NO", "CUSTOMER_NM", "MARKET_SYS", "MARKET_SYS_DESC", "ABRVITION", "SUPPLY_UNIT", "PRICE_FL", "CURRENCY", "MNY_ASK_TYP", "STLMT_UNIT_NO", "STLMT_UNIT_NM", "FST_DRAWEE_UNIT", "LEGAL_PSN", "AGENT", "ADDRESS", "ZIP_CODE", "BANK", "ACCOUNT", "TAX_ID", "PHONE", "FAX", "ORDER_WGT", "ORDER_MNY_TAX", "ORDER_MNY_NTAX", "MNY_ASK_DESC", "SIGN_PERSON", "SIGN_DATE", "SIGN_ADDRESS", "ORDER_YEAR", "ORDER_BDATE", "ORDER_EDATE", "ORDER_SCAN_NO", "CUSTOMER_MODEL_FL", "ORDER_MODEL_NO", "ORDER_MODEL_DESC", "SALE_ORG", "SALE_ORG_DESC", "SALE_AREA_NO", "SALE_AREA_DESC", "MEMO", "BE_ORDER_STS", "BE_OBJ_STS_OP", "ORDER_STS", "ORDER_STS_DESC", "OBJ_STS_OP", "APPROVAL_MAN", "APPROVAL_TIME", "EFFECT_MAN", "EFFECT_TIME", "CLOSE_MAN", "CLOSE_TIME", "VALIDFLAG", "CREATE_NAME", "CREATE_DEPT", "CREATE_TIME", "UPDATE_NAME", "UPDATE_TIME", "DELETE_NAME", "DELETE_TIME", "ORD_PK_OLD", "BE_ORDER_STS_DESC", "PRICE_NO", "ORDER_CHGE_NO", "ISLOCK", "PRINT_PRICE", "PRICE_PLAN", "CONTAIN_TAX", "ORDER_ARCHIVE", "PASS_TIME", "CONFIRM_TIME", "TRADE_TYPE", "ORDER_CLASSIFY", "BUSINESS_TYPE", "PRINT_TIME", "PRINT_COUNT", "LOCAL_LAW", "ORDER_FILE", "IPMAC", "AREA_FLAG", "USERORDER_FILE", "DOCUMENTARY_NAME", "DOCUMENTARY_DEPT", "TH_EXAMINE_STATUS", "TH_ORDER_FALG_ZC", "ORDER_REQUIRE_STATUS", "PORT_OF_DESTINATION", "PENALTY_DESC", "CREATE_NAME_OLD", "SALE_AREA_NO_OLD", "SALE_AREA_DESC_OLD", "TH_INTERFACE_FLAG",
    
    // 订单行字段
    "ORD_PK", "ORD_LN_PK", "ORDER_SEQ", "CUST_ALPHA_NO", "PSC", "PSC_DESC", "MSC", "MSC_DESC", "MSC_STYLE", "USE_CODE", "USE_DESC", "PRD_CLS", "PRODUCCODE", "PRODUCNAME", "STD_CODE", "STD_NAME", "STEELCODE", "STEELNAME", "STD_STYLE", "STD_STYLE_DESC", "ORDER_SPEC_CODE", "ORDER_SPEC_DESC", "SPEC_CODE", "SPEC_NAME", "DIMATER", "HEIGHT", "MODEL_CODE", "MODEL_DESC", "LEN_NO", "LEN_DESC", "LEN_UNIT", "LEN_MIN", "LEN_MAX", "SHRT_LEN_RATO", "SHORTEST", "LONG_LEN_RATO", "LONGEST", "SHRT_STOVE", "AVG_LEN_MIN", "DIFRNC_LEN_MAX", "ENDLEN_SPECSTD", "SIGL_LEN", "ORDER_UNIT", "ORDER_QTY", "ORD_ENSU_TOTL_LEN", "PRDCT_WGT", "DELVRY_RANGE_TPE", "DELVRY_RANGE_MIN", "DELVRY_RANGE_MAX", "FINAL_USER", "FINAL_USER_DESC", "OUTS_BILLET_FL", "OUTS_STL_PIPE_FL", "ASSEL_FL", "PRODUCER_FL", "PRDCR_NO", "PRDCR_DESC", "MPS_FL", "MPS_NO", "MPS_DESC", "DELVRY_BDATE", "DELVRY_EDATE", "DELVRY_DESC", "TRANS_DESC", "ADD_ASK_NO", "ORDER_ADD_DESC", "ORDER_SPECIAL_FL", "ORDER_SPECIAL_HEAD", "ORDER_SPECIAL_PATH", "ORDER_SPECIAL_DESC", "ZBS", "ORDER_PRICE", "TRANS_PRICE", "TEST_ID", "TEST_PRICE", "OUT_PRICE", "BALNCE_PRICE", "BE_ORDER_LN_STATUS", "BE_OBJ_STS_OP", "ORDER_LN_STATUS", "OBJ_STS_OP", "MEMO", "VALIDFLAG", "CREATE_NAME", "CREATE_TIME", "UPDATE_NAME", "UPDATE_TIME", "DELETE_NAME", "DELETE_TIME", "ORD_LN_PK_OLD", "ISLOCK", "CUST_ALPHA_NO2", "ORDER_CHGE_NO", "CHGE_ORD_LN_PK", "BALNCE_PRICE_STS", "BALNCE_PRICE_ORG", "BALNCE_PRICE_ORG_STS", "PAK_PRICE", "AW", "MPS_NO_INPUT", "MAN_INSPECTION", "INSPECTION_VALUE", "MPS_NO_FILE", "QUALITY_USER", "QUALITY_USER_DESC", "OUTPUT_STD_NO", "PROJECT_NO", "CRAFT_NO", "CRAFT_NO_V", "ORDER_COUNT", "MPS_ID", "LOGOUT_REVIEW_NO", "PIC_FD_CHECK", "BALNCE_PRICE_UNIT", "TON_PRICE", "KEEP_TYPE", "AVG_LEN_MAX", "WBS_CODE", "OUTBOUND_CALCULATE", "ORDER_TEAM", "MODE_OF_TRANSPORT", "AUTO_MEASURE", "KEY_PROJECT_CODE", "KEY_PROJECT_NAME", "KEY_PROJECT_STATUS",
    
    // 订单行交付字段
    "ORD_PK", "ORD_LN_PK", "ORD_LN_DLY_PK", "DELIVERY_NO", "DELVRY_BDATE", "DELVRY_EDATE", "DELVRY_QTY", "ORDER_UNIT", "WGT_ESTMT", "ENSU_TOTL_LEN", "RECEIV_NO", "RECEIV_NM", "DELVRY_ADDR", "AGENCY_FL", "RECEIV_ADDR", "TRANSIT_TYP", "TRANSIT_PLAN", "TRANSIT_MNY", "BE_DELIVERY_STATUS", "BE_OBJ_STS_OP", "DELIVERY_STATUS", "OBJ_STS_OP", "VALIDFLAG", "CREATE_NAME", "CREATE_TIME", "UPDATE_NAME", "UPDATE_TIME", "DELETE_NAME", "DELETE_TIME", "ISLOCK", "PICKUP_FLAG", "EXTRA_FLAG", "IS_ADD", "IS_LIKU", "NLK_RSN", "NLK_CHECK", "LK_ID", "TD_ID", "PRESENT_STATUS"
];

const 老产销实际对比列 = `
SELECT
    t.ORDER_NO,
    T1.ORDER_SEQ,
    T2.DELIVERY_NO,
    t.CUSTM_ORDER_NO,
    t.ORDER_TYP_DESC,
    t.CUSTOMER_NM,
    t.CUSTOMER_NO,
    t.CURRENCY,
    t.PRICE_FL,
    t.MNY_ASK_TYP,
    t.SUPPLY_UNIT,
    t.STLMT_UNIT_NM,
    t.STLMT_UNIT_NO,
    T.FST_DRAWEE_UNIT,
    t.MNY_ASK_DESC,
    t.SALE_ORG_DESC ,
    t.SALE_AREA_DESC,
    t.ORDER_STS_DESC ,
    t.EFFECT_MAN,
    t.CREATE_NAME ,
    t.UPDATE_NAME ,
    t.DOCUMENTARY_NAME ,
    t.CONTAIN_TAX,
    T.ORDER_CLASSIFY,
    t.PRINT_PRICE,
    t.PRICE_PLAN ,
    t.ORDER_ARCHIVE,
    T1.PRD_CLS,
    T1.PSC_DESC ,
    T1.MSC_DESC ,
    T1.USE_DESC ,
    T1.PRODUCNAME ,
    T1.STD_NAME ,
    T1.STEELNAME ,
    T1.STD_STYLE_DESC ,
    T1.ORDER_SPEC_DESC ,
    T1.SPEC_NAME ,
    T1.DIMATER ,
    T1.HEIGHT ,
    T1.MODEL_DESC ,
    T1.LEN_DESC ,
    T1.LEN_UNIT ,
    T1.LEN_MIN ,
    T1.LEN_MAX ,
    T1.SHRT_LEN_RATO ,
    T1.SHORTEST ,
    T1.LONG_LEN_RATO ,
    T1.LONGEST ,
    T1.SHRT_STOVE ,
    T1.AVG_LEN_MIN ,
    T1.AVG_LEN_MAX ,
    T1.ENDLEN_SPECSTD ,
    T1.SIGL_LEN ,
    T1.ORDER_UNIT ,
    T1.ORDER_QTY ,
    T1.ORD_ENSU_TOTL_LEN ,
    T1.PRDCT_WGT ,
    T1.DELVRY_RANGE_TPE ,
    T1.DELVRY_RANGE_MIN ,
    T1.DELVRY_RANGE_MAX ,
    T1.FINAL_USER_DESC ,
    T1.FINAL_USER,
    T1.OUTS_BILLET_FL,
    T1.OUTS_STL_PIPE_FL,
    T1.ASSEL_FL,
    T1.PRODUCER_FL,
    T1.PRDCR_DESC AS 监制说明,
    T1.MPS_FL,
    T1.MPS_NO ,
    T1.MPS_DESC ,
    T1.ORDER_ADD_DESC ,
    T1.ORDER_SPECIAL_HEAD ,
    T1.ORDER_PRICE ,
    T1.TRANS_PRICE ,
    T1.TEST_PRICE ,
    T1.OUT_PRICE ,
    T1.BALNCE_PRICE ,
    T1.PAK_PRICE ,
    T1.TON_PRICE ,
    T1.KEEP_TYPE,
    T1.ORDER_LN_STATUS,T1.BALNCE_PRICE_STS,T1.MAN_INSPECTION,
    T1.INSPECTION_VALUE ,
    T1.QUALITY_USER_DESC  ,
    T1.WBS_CODE ,
    T1.OUTBOUND_CALCULATE,T1.ORDER_TEAM,T1.MODE_OF_TRANSPORT,T1.AUTO_MEASURE,

    T1.KEY_PROJECT_NAME  ,

    T1.OUTPUT_STD_NO,


    t2.DELVRY_EDATE ,
    t2.DELVRY_QTY ,
    t2.WGT_ESTMT ,
    t2.ENSU_TOTL_LEN ,
    t2.RECEIV_NM ,
    t2.DELVRY_ADDR ,t2.AGENCY_FL,
    t2.RECEIV_ADDR ,t2.TRANSIT_TYP,
    t2.TRANSIT_PLAN ,t2.PICKUP_FLAG
FROM
    SLM_ORDER_HEAD@TGCXDB_105 t
        INNER JOIN SLM_ORDER_LINE@TGCXDB_105 T1 ON T.ORD_PK = T1.ORD_PK
        INNER JOIN SLM_ORDER_LINE_DELIVERY@TGCXDB_105 T2 ON T.ORD_PK = T2.ORD_PK
        AND T1.ORD_LN_PK = T2.ORD_LN_PK
WHERE
    t.VALIDFLAG = '1'  and t1.VALIDFLAG = '1' and t2.VALIDFLAG='1'
  and ROWNUM<10

`
 
const fT = (result,tableName)=>{
    const arr =   (result.rows[0][0]==0?needNULLFlag:needNotNULLFlag)
           arr  .push(
                {
                  column:tableName+'.'+item,
                  size:result.rows[0][0]
                }
              
              );
              console.log('arr:',arr)
}

  const date = moment().format('YYYY-MM-DD HH-mm');
  const dateSubFix = `99999-${date}`;
// 使用连接池
async function run() {
  let pool;
  try {
    pool = await oracledb.createPool(DB_CONFIG);
    let connection;
    try {
      connection = await pool.getConnection();
    //   connection.execute('select * from AA_AARF ',(err,result)=>{
    //     console.log('result:',result)
    //   })
     // const 新产销与老产销Result = await 


      const 老产销实际对比列Data = await connection.execute(老产销实际对比列);
      const ocx = 老产销实际对比列Data.metaData.map(s=>s.name);

      
      const dif = orderFields.filter(item => !ocx.includes(item));
      console.log('新产销总列与新产销对比列的差集:',dif.length,dif)
      const data = [];
    
        const needNULLFlag = []
        const needNotNULLFlag = []

      for(var item of new Set(dif)){
      //   const sqlGroupBy =  `
      //  select count(m.${item}),m.${item} as "columnKey" from (
      //   select t1.*,t2.* from SO_ORDER@CGXZCDB_ZSJ t1  left join  SO_ORDER_dtl@CGXZCDB_ZSJ   t2 on t1.order_no=t2.order_no
      //  ) m group by m.${item}
      //   `

      //     const sqlNull =  `
      //  select count(m.${item}) as "columnKey" from (
      //   select t1.*,t2.* from SO_ORDER@CGXZCDB_ZSJ t1  left join  SO_ORDER_dtl@CGXZCDB_ZSJ   t2 on t1.order_no=t2.order_no
      //  ) m where m.${item} is null or m.${item}=''
      //   `

        //  SLM_ORDER_HEAD@TGCXDB_105 t
        // INNER JOIN SLM_ORDER_LINE@TGCXDB_105 T1 ON T.ORD_PK = T1.ORD_PK
        // INNER JOIN SLM_ORDER_LINE_DELIVERY@TGCXDB_105 T2 ON T.ORD_PK = T2.ORD_PK

         const sqlNull = (tableName)=> `

         SELECT
    CASE
        WHEN COUNT(CASE WHEN ${item} IS NULL OR ${item} = '' THEN 1 END) = COUNT(1)
            THEN '全部为空'
        ELSE '存在非空值'
        END AS result
FROM ${tableName} where 
        VALIDFLAG='1'   
        `

       

        try{
          let result = await connection.execute(sqlNull('SLM_ORDER_HEAD@TGCXDB_105'))
           console.log(`result:${item};${sqlNull('SLM_ORDER_HEAD@TGCXDB_105')}`,result.rows)
          //  if(result.rows[0][0]==0){
          //     needNULLFlag.push(
          //       {
          //         column:'SLM_ORDER_HEAD@TGCXDB_105.'+item,
          //         size:result.rows[0][0]
          //       }
          //     )
          //  }
         // fT(result,'SLM_ORDER_HEAD@TGCXDB_105')
         const item0 =  {
                  column:'SLM_ORDER_HEAD@TGCXDB_105.'+item,
                  size:result.rows[0][0]
                };
          if(result.rows[0][0]=='全部为空'){
            needNULLFlag.push(item0)
          }else{
            needNotNULLFlag.push(item0)
          }
          


            result = await connection.execute(sqlNull(' SLM_ORDER_LINE@TGCXDB_105'))
           console.log(`result:${item};${sqlNull(' SLM_ORDER_LINE@TGCXDB_105')}`,result.rows)
           //fT(result,'SLM_ORDER_LINE@TGCXDB_105')

            const item1 =  {
                  column:'SLM_ORDER_LINE@TGCXDB_105.'+item,
                  size:result.rows[0][0]
                };
          if(result.rows[0][0]=='全部为空'){
            needNULLFlag.push(item1)
          }else{
            needNotNULLFlag.push(item1)
          }

         


            result = await connection.execute(sqlNull('SLM_ORDER_LINE_DELIVERY@TGCXDB_105'))
           console.log(`result:${item};${sqlNull('SLM_ORDER_LINE_DELIVERY@TGCXDB_105')}`,result.rows)

           //fT(result,'SLM_ORDER_LINE_DELIVERY@TGCXDB_105')
            const item2 =  {
                  column:'SLM_ORDER_LINE_DELIVERY@TGCXDB_105.'+item,
                  size:result.rows[0][0]
                };
          if(result.rows[0][0]=='全部为空'){
            needNULLFlag.push(item2)
          }else{
            needNotNULLFlag.push(item2)
          }

        }catch(e){
           // console.log('e:',e)
        }



       
       
      }

      
       console.log('needNULLFlag:',needNULLFlag)
       // console.log('needNotNULLFlag:',needNotNULLFlag)


    } catch (err) {
      throw (err);
    } finally {
      if (connection) {
        try {
          await connection.close(); 
        } catch (err) {
          throw (err);
        }
      }
    }
  } catch (err) {
    console.log(err)
    throw err;
  } finally {
    await pool.close();
  }
}
run()